const { Service } = require('egg');

class UserService extends Service {
    /**
     * 持久化用户信息
     * @param {string} username
     * @param {string} password
     * @param {string} relname
     */
    async create({ username, password, relname }) {
        let sql = `insert into user (username, password, relname) values (?, ?, ?)`;
        let params = [username, password, relname];
        return await this.app.mysql.query(sql, params);
    }

    /**
     * 持久化用户角色身份
     * @param {number} uid
     * @param {number} rid
     */
    async saveRole(uid, rid) {
        let sql = `insert into r_user_role (uid, rid) values (?, ?)`;
        let params = [uid, rid];
        return await this.app.mysql.query(sql, params);
    }

    /**
     * 根据用户名，获取指定用户信息
     * @param {string} username 用户名
     */
    async readByUsername(username) {
        let sql = `select * from user where username = ? limit 0, 1`;
        let params = [username];
        let res = await this.app.mysql.query(sql, params);
        return res[0];
    }

    /**
     * 根据用户id，获取用户角色（身份）信息
     * @param {number} uid 用户id
     */
    async readRoleByUid(uid) {
        let sql = `
            select r.id as rid, r.name as rname 
            from role as r 
                join r_user_role as rur on rur.rid = r.id 
            where rur.uid = ?
            limit 0, 1
        `;
        let params = [uid];
        let res = await this.app.mysql.query(sql, params);
        return res[0];
    }

    /**
     * 根据用户id，获取用户可访问的前端路由页面
     * @param {number} uid 用户id
     */
    async readMenusByUid(uid) {
        let sql = `
            select a.name, a.path
            from user as u
                join r_user_role as rur on rur.uid = u.id
                join r_role_acl as rra on rur.rid = rra.rid
                join acl as a on a.id = rra.aid
            where a.type = 1 and u.id = ?
        `;
        let params = [uid];
        return await this.app.mysql.query(sql, params);
    }

    /**
     * 根据查询参数，读取学生列表（角色是 2、3）
     * @param {object} param0 查询参数对象
     */
    async readStuListByQuery({ query, pagesize, pagenum }) {
        let select_sql =
            'select u.id as uid, u.username, u.relname, r.id as rid, r.name as rname';
        let from_sql = ' from user as u';
        let join_sql = `
            join r_user_role as rur on u.id = rur.uid
            join role as r on rur.rid = r.id
        `;
        let where_sql = ' where rur.rid != 1';
        let params = [];

        // 模糊查询
        if (query && query.trim()) {
            where_sql += ' and (u.username like ? or u.relname like ?)';
            let likeStr = '%' + query + '%';
            params.push(likeStr, likeStr);
        }
        // 如果有分页参数，就返回分页数据
        if (!isNaN(pagesize) && !isNaN(pagenum)) {
            // 计数
            let count_sql = 'select count(*) as count';
            let countArr = await this.app.mysql.query(
                count_sql + from_sql + join_sql + where_sql,
                params,
            );

            // 分页数据
            let startIndex = (pagenum - 1) * pagesize;
            params.push(startIndex, parseInt(pagesize));

            // 分页 sql
            let resArr = await this.app.mysql.query(
                select_sql + from_sql + join_sql + where_sql + ' limit ?, ?',
                params,
            );

            return { total: countArr[0].count, list: resArr };
        }

        return await this.app.mysql.query(
            select_sql + from_sql + join_sql + where_sql,
            params,
        );
    }

    /**
     * 根据用户id，获取用户的后端路由
     * @param {number} uid 用户id
     */
    async readAclsByUid(uid) {
        let sql = `
            select a.path, a.method
            from acl as a
                join r_role_acl as rra on rra.aid = a.id and a.type = 2
                join r_user_role as rur on rur.rid = rra.rid
            where rur.uid = ?
        `;
        let params = [uid];
        return await this.app.mysql.query(sql, params);
    }

    async destroy(uid) {
        let sql = 'delete from user where id = ?';
        let params = [uid];
        return await this.app.mysql.query(sql, params);
    }
}

module.exports = UserService;
